package com.powerunion.datacollection.dao.impl;

import java.sql.*;

import org.apache.log4j.Logger;

import com.powerunion.datacollection.common.SystemException;
import com.powerunion.datacollection.util.DBUtil;
import com.powerunion.util.log.LogHelper;
import com.powerunion.util.DBUtils;
import com.powerunion.util.query.Query;
import com.powerunion.util.query.jdbc.QueryParameters;
import com.powerunion.util.query.Page;
import com.powerunion.util.query.Sort;
import com.powerunion.util.query.jdbc.QueryJDBCHelper;

import java.util.ArrayList;
import java.util.List;

/**
 * <p>Title: 提供一些通用的数据库处理方法</p>
 *
 * <p>Description: 面向于oracle实现，所有流水号都是用序列</p>
 *
 * <p>Copyright: Copyright (c) 2005</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
public class JdbcBase {
	private static final Logger logger = LogHelper.getInstance().getLogger(
			JdbcBase.class);

	private DBUtil dbUtil = null;

	public JdbcBase() {
		super();
		dbUtil = new DBUtil();
	}

	public void finalize() throws Throwable {
		this.dbUtil = null;
		super.finalize();
	}

	public Connection getConnection() throws SQLException {
		return this.dbUtil.getConnection();
	}

	/**
	 * 得到序号
	 * @param tableName String
	 * @return long
	 * @throws SQLException
	 */
	public long getSeq(String tableName){
		long code = -1;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rsData = null;
		String sql = "select seq_valu from sys_seq where seq_code=?";
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, tableName);
			rsData = ps.executeQuery();
			if (rsData.next()) {
				code = rsData.getLong(1) + 1;
				ps.close();
				ps = conn
						.prepareStatement("update sys_seq set seq_valu=? where seq_code=?");
				ps.setLong(1, code);
				ps.setString(2, tableName);
				ps.executeUpdate();
			} else {
				code = 1;
				ps = conn
						.prepareStatement("insert into sys_seq(seq_valu,seq_desc,seq_code) values(?,?,?)");
				ps.setLong(1, code);
				ps.setString(2, "表" + tableName + "编号");
				ps.setString(3, tableName);
				ps.executeUpdate();
			}
			
		} catch (Exception e) {
			logger.error("", e);
			throw new SystemException("Exception get seq value of " + tableName
					+ "!");
		} finally {
			DBUtils.close(rsData, ps, conn);
		}
		return code;
	}

	/**
	 * 得到关键字
	 * @param name String
	 * @return long
	 * @throws Exception
	 */
	public long seqBySequence(String name) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rsData = null;
		String sql = "select " + name + ".nextval from dual";
		long seq = -1;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			rsData = ps.executeQuery();
			if (rsData.next()) {
				seq = rsData.getLong(1);
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new SQLException("根据序列得到表关键字编号失败！");
		} finally {
			DBUtils.close(rsData, ps, conn);
		}
		return seq;
	}

	/**
	 * 添加and 条件
	 * @param sql StringBuffer
	 * @param where String
	 * @return StringBuffer
	 */
	protected static StringBuffer sqlAndWhere(StringBuffer sql, String where) {
		if (sql.length() > 0) {
			sql.append(" and ");
		}
		sql.append(where);
		return sql;
	}

	/**
	 * 排序
	 * @param extName String
	 * @param sort Sort
	 * @param defaultOrder String
	 * @return String
	 */
	protected static String orderBy(String extName, Sort sort,
			String defaultOrder) {
		String order = QueryJDBCHelper.orderBy(extName, sort);
		if (order == null || order.trim().equals("")) {
			order = defaultOrder;
		}
		return " order by " + order;
	}

	/**
	 * 根据关键字删除
	 * @param codes long[]
	 * @param sql String
	 * @return int[]
	 * @throws Exception
	 */
	protected int[] delByKey(long[] codes, QueryParameters exParam, String sql)
			throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rsData = null;
		int[] values = null;
		try {
			if (codes != null && codes.length > 0) {
				conn = getConnection();
				ps = conn.prepareStatement(sql);
				for (int i = 0; i < codes.length; i++) {
					ps.setLong(1, codes[i]);
					if (exParam != null) {
						QueryJDBCHelper.setPSParameters(ps, exParam);
					}
					ps.addBatch();
				}
				values = ps.executeBatch();
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new SQLException("删除数据库记录失败！");
		} finally {
			DBUtils.close(rsData, ps, conn);
		}
		return values;
	}

	protected int[] delByKey(long[] codes, String sql) throws Exception {
		return this.delByKey(codes, sql);
	}

	/**
	 * 列表记录
	 * @param sql String
	 * @param parameters QueryParameters
	 * @param method String
	 * @return List
	 * @throws SQLException
	 */
	protected List list(String sql, QueryParameters parameters, String method)
			throws SQLException {
		Connection conn = null;
		try {
			conn = getConnection();
			return QueryJDBCHelper.list(sql, conn, parameters, this, method);
		} catch (SQLException e) {
			throw e;
		} finally {
			DBUtils.close(conn);
		}
	}

	protected List list(String sql, String method) throws SQLException {
		return this.list(sql, null, method);
	}

	/**
	 * 用关键字查找对象
	 * @param sql String
	 * @param parameters QueryParameters
	 * @param method String
	 * @return Object
	 * @throws SQLException
	 */
	protected Object findByKey(String sql, QueryParameters parameters,
			String method) throws SQLException {
		List data = this.list(sql, parameters, method);
		if (data.size() == 1) {
			return data.get(0);
		} else
			return null;
	}

	/**
	 * 查询数据
	 * @param query Query
	 * @return Page
	 * @throws Exception
	 */
	protected Page search(String sql, QueryParameters params, Query query,
			String method) throws SQLException {
		Connection conn = null;
		try {
			conn = this.getConnection();
			return QueryJDBCHelper.search(sql, conn, params, query, this,
					method);
		} catch (SQLException e) {
			throw e;
		} finally {
			DBUtils.close(conn);
		}
	}

	/**
	 * 执行sql
	 * @param sql String
	 * @param params QueryParameters
	 * @throws SQLException
	 */
	protected int execute(String sql, QueryParameters params)
			throws SQLException {
		Connection conn = null;
		try {
			conn = getConnection();
			return QueryJDBCHelper.update(sql, conn, params);
		} catch (SQLException e) {
			throw e;
		} finally {
			DBUtils.close(conn);
		}
	}

	protected int[] executeBatch(String sql, List batchParams)
			throws SQLException {
		Connection conn = null;
		try {
			conn = getConnection();
			return QueryJDBCHelper.updateBatch(sql, conn, batchParams);
		} catch (SQLException e) {
			throw e;
		} finally {
			DBUtils.close(conn);
		}
	}

	protected List batchParams(String[] codes, int sqlType) {
		if (codes != null && codes.length > 0) {
			List batchParams = new ArrayList();
			for (int i = 0; i < codes.length; i++) {
				QueryParameters params = new QueryParameters();
				params.addParameter(codes[i], sqlType);
				batchParams.add(params);
			}
			return batchParams;
		} else {
			return null;
		}
	}

	protected int[] executeBatch(String sql, List batchParams, Connection conn)
			throws SQLException {

		try {

			return QueryJDBCHelper.updateBatch(sql, conn, batchParams);
		} catch (SQLException e) {
			throw e;
		}
	}
}
